package com.hulu.yec.stu.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

import com.hulu.yec.stu.entity.ExamRecordEntity;
import com.hulu.yec.common.Constant;
import com.hulu.yec.common.PageModel;
import com.hulu.yec.common.util.LogUtil;

public class ExamRecordDao {

	public static  PageModel<ExamRecordEntity> list(ExamRecordEntity entity,
			int pageNo, int pageSize) throws SQLException, ClassNotFoundException {
		//数据库查询
		StringBuilder sb = new StringBuilder();
		//参数集合
		List<Object>paramList = new ArrayList<Object>();
		sb.append("SELECT r.user_id, r.exam_plan_id, r.get_point, r.submit_time, n.exam_time_stop, n.exam_time_start, r.start_time, ");
		sb.append("n.id AS 'exam_plan.id', p.name AS 'qes_exam_paper.name', ");
		sb.append("p.total_point AS'qes_exam_paper.total_point', ");
		sb.append("p.pass_point AS'qes_exam_paper.pass_point' ");
		sb.append("FROM exam_plan n, exam_records r,qes_exam_paper p ");
		sb.append("WHERE r.exam_plan_id = n.id ");
		sb.append("AND n.exam_paper_id = p.id ");
		//条件查询
		if(StringUtils.isNoneBlank(entity.getPaperName())){
			sb.append("and p.name like '%"+entity.getPaperName()+"%'");
		}
		if(entity.getIsPass()  != 0){
			sb.append("and r.is_pass = ? ");
			paramList.add(entity.getIsPass());
			
		}
		LogUtil.i(Constant.LOGGER, sb.toString());
		LogUtil.i(Constant.LOGGER, paramList.toString());
		 sb.append("limit ?,?");
		 paramList.add((pageNo - 1)*pageSize);
	     paramList.add(pageSize);
			
	     
	     try {
			Class.forName(Constant.DRIVER);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	     Connection connection = null;
	     PreparedStatement preparedStatement = null;
	     ResultSet resultSet = null;
	     List<ExamRecordEntity> recordList =  new ArrayList<>();
	     
	     
	     try {
	    	 connection =DriverManager.getConnection(Constant.URL, Constant.USER, Constant.PASSWORD);
			preparedStatement = connection.prepareStatement(sb.toString());
			if(paramList != null){
				for(int i = 0; i < paramList.size(); i++){
					preparedStatement.setObject(i+1,paramList.get(i));
				}
			}
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next()){
				ExamRecordEntity examRecodEntity = new ExamRecordEntity();
				examRecodEntity.setPaperName(resultSet.getString("qes_exam_paper.name"));
				examRecodEntity.setStartTime(resultSet.getTimestamp("start_time"));
				examRecodEntity.setSubmitTime(resultSet.getTimestamp("submit_time"));
				examRecodEntity.setTotalPoint(resultSet.getInt("qes_exam_paper.total_point"));
				examRecodEntity.setPassPoint(resultSet.getInt("qes_exam_paper.pass_point"));
				examRecodEntity.setGetPoint(resultSet.getInt("get_point"));
				int totalPoint = resultSet.getInt("qes_exam_paper.total_point");
				int passPoint = resultSet.getInt("qes_exam_paper.pass_point");
				int getPoint = resultSet.getInt("get_point");
				//通过
				if(getPoint >= passPoint && getPoint <= totalPoint){
					examRecodEntity.setIsPass(1);
				}
				//未通过
				if(getPoint < passPoint && getPoint >= 0){
					examRecodEntity.setIsPass(2);
				}
				//超时未提交
				if(resultSet.getTimestamp("submit_time").after(resultSet.getTimestamp("exam_time_stop"))){
					examRecodEntity.setIsPass(3);
				}
				recordList.add(examRecodEntity);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	     finally{
				if(resultSet != null){
					resultSet.close();
				}
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}
			
	   
			return new PageModel<ExamRecordEntity>(recordList, pageNo, pageSize, getTotalRecords(entity));
		}
	public static int getTotalRecords(ExamRecordEntity entity) throws SQLException, ClassNotFoundException{
		StringBuilder sb = new StringBuilder();
		List<Object> paramList = new ArrayList<Object>();
		sb.append("select count(*) from exam_plan n,qes_exam_paper p,exam_records r where r.exam_plan_id = n.id and n.exam_paper_id = p.id ");
		String paperName = entity.getPaperName();
		if(StringUtils.isNoneBlank(paperName)){
			sb.append(" and p.name like '%"+paperName+"%'");
		}
		int isPass = entity.getIsPass();
		if(isPass !=0){
			sb.append(" AND r.is_pass =?  ");
			paramList.add(isPass);
		}
		LogUtil.i(Constant.LOGGER, sb.toString());
		Class.forName(Constant.DRIVER);
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int TotalRecords = 0;
		try{
			connection = DriverManager.getConnection(Constant.URL,Constant.USER,Constant.PASSWORD);
			preparedStatement = connection.prepareStatement(sb.toString());
			if(paramList != null){
				for(int i = 0; i < paramList.size(); i++){
					preparedStatement.setObject(i+1,paramList.get(i));
				}
			}
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			TotalRecords = resultSet.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(resultSet != null){
				resultSet.close();
			}
			if(preparedStatement != null){
				preparedStatement.close();
			}
			if(connection != null){
				connection.close();
			}
		}
		return TotalRecords;
	}
	
}


